import pymysql

# 数据库设置
dbname = "measurement"
# host = "rm-bp151dcc75aycqd80to.mysql.rds.aliyuncs.com"
# username = "measurement"
# password = "Measure123"

host = "127.0.0.1"
username = "root"
password = "123456"
# username = "measurement"
# password = "Measure123"


MAX_LEN = 50000     # 每次插入数据库的最大数据条数（批量插入功能）和insert_db_in_batch配套使用


def connDb():
    db = pymysql.connect(host=host, port=3306, user=username, passwd=password, db=dbname, charset='utf8')
    return db

def insert_db(table, table_fields, values):
    db = connDb()
    cursor = db.cursor()

    sqlstr = "insert into %s (%s) values (%s)" % (table, table_fields, values)
    #print("sqlstr: ", sqlstr)

    ret = -1
    try:
        cursor.execute(sqlstr)
        ret = cursor.lastrowid
    except Exception as e:
        print("DB_insert_Error: %s", e)
        if len(sqlstr) > 200:
            print("sqlstr: ", sqlstr[0:1000])
        else:
            print("sqlstr: ", sqlstr)
        db.rollback()
    finally:
        db.commit()
        db.close()

    return ret


def insert_db_in_batch(db_table, db_cols_arr, data_arr):
    """
    批量插入数据到数据库，基于insert_db实现。效率更高，推荐使用！
    """
    if not isinstance(db_cols_arr, list) or len(db_cols_arr) <= 0:
        print("参数错误：未找到insert指定的列，或插入的值为空")
        return -1

    col_str = ','.join(db_cols_arr)
    data_str = '),('.join(data_arr)

    head_id = insert_db(db_table, col_str, data_str)
    print("插入数据条数：%d" % len(data_arr))
    print("插入的最后记录Id： %d" % head_id)

    return head_id


def select_db(table, where_condition=""):
    db = connDb()
    cursor = db.cursor()

    if where_condition == "":
        sqlstr = "select * from %s" % table
    else:
        sqlstr = "select * from %s where %s" % (table, where_condition)

    print("select_db: %s" % sqlstr)
    cursor.execute(sqlstr)
    db.close()
    # data = cursor.fetchone()
    data = cursor.fetchall()

    return data


def select_db_v2(table, select_str="*", where_condition=""):
    db = connDb()
    cursor = db.cursor()

    if where_condition == "":
        sqlstr = "select %s from %s" % (select_str, table)
    else:
        sqlstr = "select %s from %s where %s" % (select_str, table, where_condition)

    print("select_db: %s" % sqlstr)
    cursor.execute(sqlstr)
    db.close()
    # data = cursor.fetchone()
    data = cursor.fetchall()

    return data


def update_db(table, update_str, where_condition=""):
    db = connDb()
    cursor = db.cursor()

    if where_condition == "":
        sqlstr = "update %s set %s" % (table, update_str)
    else:
        sqlstr = "update %s set %s where %s" % (table, update_str, where_condition)

    try:
        cursor.execute(sqlstr)
    except Exception as e:
        print("DB_update_Error: %s", e)
        print("sqlstr: ", sqlstr)
        db.rollback()
    finally:
        db.commit()
        db.close()


# 使用replace into更新
def update_db_in_batch(db_table, db_cols_arr, data_arr):
    """
    批量更新数据到数据库，基于replace_db实现。效率更高，推荐使用！
    参考：https://wenku.baidu.com/view/556a89f2baf3f90f76c66137ee06eff9aef849c0.html
    """
    if not isinstance(db_cols_arr, list) or len(db_cols_arr) <= 0:
        print("参数错误：未找到insert指定的列，或插入的值为空")
        return -1

    col_str = ','.join(db_cols_arr)
    data_str = '),('.join(data_arr)

    head_id = replace_db(db_table, col_str, data_str)
    print("插入数据条数：%d" % len(data_arr))
    print("插入的最后记录Id： %d" % head_id)

    return head_id


def replace_db(table, table_fields, values):
    db = connDb()
    cursor = db.cursor()

    sqlstr = "replace into %s (%s) values (%s)" % (table, table_fields, values)
    #print("sqlstr: ", sqlstr)

    ret = -1
    try:
        cursor.execute(sqlstr)
        ret = cursor.lastrowid
    except Exception as e:
        print("DB_update(replace)_Error: %s", e)
        # print("sqlstr: ", sqlstr)
        if len(sqlstr) > 200:
            print("sqlstr: ", sqlstr[0:1000])
        else:
            print("sqlstr: ", sqlstr)
        db.rollback()
    finally:
        db.commit()
        db.close()

    return ret


# 使用insert into xxx on duplicate key update更新
def update_db_in_batch_v2(db_table, db_cols_arr, data_arr, on_update_str):
    """
    批量更新数据到数据库，基于replace_db实现。效率更高，推荐使用！
    参考：https://wenku.baidu.com/view/556a89f2baf3f90f76c66137ee06eff9aef849c0.html
    """
    if not isinstance(db_cols_arr, list) or len(db_cols_arr) <= 0:
        print("参数错误：未找到insert指定的列，或插入的值为空")
        return -1

    col_str = ','.join(db_cols_arr)
    data_str = '),('.join(data_arr)

    head_id = insert_db_on_update(db_table, col_str, data_str, on_update_str)
    print("插入数据条数：%d" % len(data_arr))
    print("插入的最后记录Id： %d" % head_id)

    return head_id


# insert into xxx on duplicate key update
# 注意：此方法（ON DUPLICATE KEY UPDATE）只适用于 Mysql4.1 之后的数据库，别的数据库不行！
def insert_db_on_update(table, table_fields, values, on_update_str):
    db = connDb()
    cursor = db.cursor()

    sqlstr = "insert into %s (%s) values (%s) on duplicate key update %s" % (table, table_fields, values, on_update_str)
    #print("sqlstr: ", sqlstr)

    ret = -1
    try:
        cursor.execute(sqlstr)
        ret = cursor.lastrowid
    except Exception as e:
        print("DB_update(replace)_Error: %s", e)
        # print("sqlstr: ", sqlstr)
        if len(sqlstr) > 200:
            print("sqlstr: ", sqlstr[0:1000])
        else:
            print("sqlstr: ", sqlstr)
        db.rollback()
    finally:
        db.commit()
        db.close()

    return ret


def clear_table(table):
    db = connDb()
    cursor = db.cursor()

    sqlstr = "truncate table %s" % table

    try:
        cursor.execute(sqlstr)
    except Exception as e:
        print("DB_update_Error: %s", e)
        print("sqlstr: ", sqlstr)
        db.rollback()
    finally:
        db.commit()
        db.close()


def delete_data_from_table(table, where_condition=""):
    db = connDb()
    cursor = db.cursor()

    if where_condition == "":
        sqlstr = "truncate table %s" % table
    else:
        sqlstr = "delete from %s where %s" % (table, where_condition)
        print("sqlstr: ", sqlstr)
    try:
        cursor.execute(sqlstr)
    except Exception as e:
        print("DB_delete_data_Error: %s", e)
        print("sqlstr: ", sqlstr)
        db.rollback()
    finally:
        db.commit()
        db.close()


def count_db_data_lines(table, where_condition=""):
    db = connDb()
    cursor = db.cursor()

    if where_condition == "":
        sqlstr = "select count(*) from %s" % table
    else:
        sqlstr = "select count(*) from %s where %s" % (table, where_condition)
        print("sqlstr: ", sqlstr)

    ret = -1
    try:
        cursor.execute(sqlstr)
        (ret,) = cursor.fetchone()
    except Exception as e:
        print("DB_insert_Error: %s", e)
        print("sqlstr: ", sqlstr)
        db.rollback()
    finally:
        db.commit()
        db.close()

    return ret


def get_table_fields(table_name):
    """
    获取指定表的所有字段
    DESC bt_data_head;
    """
    db = connDb()
    cursor = db.cursor()

    sqlstr = "DESC %s" % table_name

    print("get_table_all_fields: %s" % sqlstr)
    cursor.execute(sqlstr)
    db.close()
    # data = cursor.fetchone()
    data = cursor.fetchall()

    return data
